﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace Tab2Sql
{
    /// <summary>
    /// generic db layer for sql server connections
    /// November 2008
    /// </summary>
    public class Db
    {
        /// <summary>
        /// Saved Connection String.
        /// </summary>
        private const string DefaultConnnectionString =
            @"Data Source=\\.\pipe\SQLLocal\WTF;Application Name=DSql;Initial Catalog=tab2SQL;Integrated Security=True";

        /// <summary>
        /// Current connection string.
        /// </summary>
        private string connectionString = "";

        /// <summary>
        /// Default catalog.
        /// </summary>
        private string databaseCatalog = "";

        /// <summary>
        /// Where any error message is saved.
        /// </summary>
        private string errorMsg = "";

        /// <summary>
        /// Time in milliseconds last query took to execute.
        /// </summary>
        private long queryTimeMS = 0;

        /// <summary>
        /// Record of rows returned in last statement.
        /// </summary>
        private int rowCount = 0;

        /// <summary>
        /// Server or previous query.
        /// </summary>
        private string serverID = "";

        /// <summary>
        /// Time class was initialised. Value in ticks. 
        /// </summary>
        private double timeA = getTime();

        /// <summary>
        /// Time query reached the second stage. Not in use.
        /// </summary>
        private double timeB = 0;

        /// <summary>
        /// Time query reached the third stage. Not in use.
        /// </summary>
        private double timeC = 0;

        /// <summary>
        /// Constructor.
        /// </summary>
        public Db()
        {
            ConnectionString = DefaultConnnectionString;
        }

        /// <summary>
        /// Constructor if you want a different connection string.
        /// </summary>
        /// <param name="iString"></param>
        public Db(string iString)
        {
            if (iString.Length > 4)
            {
                ConnectionString = iString;
            }
            else
            {
                ConnectionString = DefaultConnnectionString;
            }
        }
        /// <summary>
        /// Connection string. Not necessarily valid or active.
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }
        /// <summary>
        ///
        /// </summary>
        public string ErrorMsg
        {
            get { return errorMsg; }
            set { errorMsg = value; }
        }
        /// <summary>
        ///
        /// </summary>
        public int RowCount
        {
            get { return rowCount; }
            set { rowCount = value; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string DatabaseCatalog
        {
            get { return databaseCatalog; }
            set { databaseCatalog = value; }
        }
/// <summary>
///
/// </summary>
        public long QueryTimeMS
        {
            get { return queryTimeMS; }
            set { queryTimeMS = value; }
        }
        /// <summary>
        /// The short host name of current server. No prefixes/qualifiers.
        /// </summary>
        public string ServerID
        {
            get { return serverID; }
            set { serverID = value; }
        }
        /// <summary>
        /// ms at checkpoint A
        /// </summary>
        public double TimeA
        {
            get { return timeA; }
            set { timeA = value; }
        }
        /// <summary>
        /// ms at checkpoint B
        /// </summary>
        public double TimeB
        {
            get { return timeB; }
            set { timeB = value; }
        }
        /// <summary>
        /// ms at checkpoint C
        /// </summary>
        public double TimeC
        {
            get { return timeC; }
            set { timeC = value; }
        }

        /// <summary>
        /// executes Sql statement, returns a row count. For updates, deletes, etc. 
        /// </summary>
        /// <param name="cmdX"></param>
        /// <returns></returns>
        public int ExecuteSql(SqlCommand cmdX)
        {
            using (var conn = new SqlConnection(GetConnString()))
            {
                conn.Open();
                cmdX.Connection = conn;
                try
                {
                    RowCount = cmdX.ExecuteNonQuery();

                    ConnectionString = cmdX.Connection.ConnectionString.ToString();
                    DatabaseCatalog = conn.Database.ToString();
                    ServerID = conn.ServerVersion.ToString();
                }
                catch (Exception ex)
                {
                    ErrorMsg += ex.Message.ToString();
                    rowCount = -1;
                }

                return Convert.ToInt32(RowCount);
            }
        }

        /// <summary>
        /// executes sql, gives rowcount.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int ExecuteSql(string SqlString)
        {
            TimeB = getTime();

            var cmd = new SqlCommand();
            cmd.CommandText = SqlString;

            RowCount = Convert.ToInt32(ExecuteSql(cmd));
            TimeC = getTime();
            cmd.Dispose();

            return RowCount;
        }

        /// <summary>
        /// returns a connection string.
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetConnected
        {
            get
            {
                var myConn = new SqlConnection(GetConnString());
                return myConn;
            }
        }

        /// <summary>
        /// returns datatable from sql query command.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, SqlParameter[] param)
        {
            TimeB = getTime();
            var dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (var conn = new SqlConnection(GetConnString()))
                {
                    using (var cmd = new SqlCommand(SqlString, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (param.Length > 0)
                        {
                            foreach (SqlParameter p in param)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        conn.Open();
                        IDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        RowCount = dt.Rows.Count;

                        ConnectionString = conn.ConnectionString.ToString();
                        DatabaseCatalog = conn.Database.ToString();
                        DGen.DFile.WriteToLog(RowCount.ToString() + " row(s)");
                        DGen.DFile.WriteToLog(cmd.Parameters.Count.ToString() + " param(s)");
                        DGen.DFile.WriteToLog(cmd.Parameters[0].ParameterName);
                        DGen.DFile.WriteToLog(cmd.Parameters[0].Value.ToString());
                        conn.Close();
                        TimeC = getTime();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorMsg += ex.Message;
            }

            return dt;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="SqlString">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="DataTable"/>
        /// </returns>
        public DataTable RenderSQL(string SqlString)
        {
            var fakeParameters = new SqlParameter[0];
            return RenderSQL(SqlString, fakeParameters);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="SqlString">
        /// A <see cref="System.String"/>
        /// </param>
        /// <param name="args">
        /// </param>
        /// <returns>
        /// A <see cref="DataTable"/>
        /// </returns>
        public DataTable RenderSQL(string SqlString, Dictionary<string, string> args)
        {
            return RenderSQL(SqlString, convertArgs(args));
        }

        private static SqlParameter[] convertArgs(Dictionary<string, string> inArgs)
        {
            var outArgs = new SqlParameter[inArgs.Count];
            int i = 0;
            foreach (var dictE in inArgs)
            {
                outArgs[i] = new SqlParameter(dictE.Key, dictE.Value);
                i++;
            }

            return outArgs;
        }

        private static double getTime()
        {
            var epoch = (double) (DateTime.UtcNow - new DateTime(1970, 1, 1)).TotalMilliseconds;
            return epoch;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="SqlString">
        /// A <see cref="System.String"/>
        /// </param>
        /// <returns>
        /// A <see cref="SqlDataAdapter"/>
        /// </returns>
        public SqlDataAdapter GetAdapter(string SqlString)
        {
            var cmd = new SqlCommand(SqlString);
            var conn = new SqlConnection(GetConnString());
            cmd.Connection = conn;
            var da = new SqlDataAdapter(cmd);
            cmd.Dispose();
            return da;
        }

        private string GetConnString()
        {
            return ConnectionString;
        }
    }
}